package com.han.hotelplat.service.datacenter.employee.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Hibernate;
import org.hibernate.type.NullableType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.han.hotelplat.comm.Constant;
import com.han.hotelplat.comm.base.EntityManage;
import com.han.hotelplat.comm.utils.AreaHelp;
import com.han.hotelplat.comm.utils.DateUtil;
import com.han.hotelplat.model.AreaModel;
import com.han.hotelplat.service.datacenter.employee.EmployeeDataService;
@Service
public class EmployeeDataServiceImpl implements EmployeeDataService{
	@Autowired
	private  EntityManage entityManage;
	
	@Override
	public Map<String, Integer> getEmployeeGroupByState() {
		Map<String, Integer> data = new HashMap<String, Integer>();
		String sql = "SELECT e.state, COUNT(e.id) from tb_employee AS e  GROUP BY e.state";
		List<Object> ctli = entityManage.findBySql(sql.toString());
		int sumCount = 0;//总人数
		int passCount = 0;//通过
		int noPassCount = 0;//未通过
		int freezeCount = 0;//冻结
		int noSubmitCount = 0;//未提交资料的
		int waitAuthCount = 0;//待审核的
		int creatCount = 0;//已注册的
		for(int i= 0 ; i<ctli.size(); i++){
			Object[] ory = (Object[]) ctli.get(i);
			int count = 0;
			int state = -1;
			if(ory[0] != null){//状态
				state = Integer.parseInt(ory[0].toString());
			}
			if(ory[1] != null){//人数
				count = Integer.parseInt(ory[1].toString());
				
			}
			sumCount += count;//总人数
			if(state == Constant.USER_STATE_PASS){
				passCount += count;//通过的人数
			}else if(state == Constant.USER_STATE_NO_PASS){
				noPassCount += count;//未通过的人数
			}else if(state == Constant.USER_STATE_FREEZE){
				freezeCount += count;//冻结的人数
			}else if(state == Constant.USER_STATE_CREAT){
				creatCount += count;//已注册的
			}
		}
		Map<String, NullableType> fieldMappingType = new HashMap<String, NullableType>();
		fieldMappingType.put("usercount", Hibernate.INTEGER);
		//资料不全的
		sql = "SELECT COUNT(em.id) AS usercount from tb_employee AS em WHERE  (ISNULL(em.icon) or ISNULL(em.realname) or ISNULL(em.sex) or ISNULL(em.workyears) or ISNULL(em.level) or ISNULL(em.idcard) or ISNULL(em.facepic) or ISNULL(em.backpic) or ISNULL(em.area) or ISNULL(em.address)) AND em.state=1";
		ctli = entityManage.findBySql(sql.toString(),fieldMappingType);
		
		for(int i= 0 ; i<ctli.size(); i++){
			Integer ory = (Integer) ctli.get(i);
			noSubmitCount = ory;
		}
		waitAuthCount = creatCount-noSubmitCount;//待审核的
		data.put("sumCount", sumCount);
		data.put("passCount", passCount);
		data.put("waitAuthCount", waitAuthCount);
		data.put("noPassCount", noPassCount);
		data.put("noSubmitCount", noSubmitCount);
		data.put("freezeCount", freezeCount);
		return data;
	}
	
	@Override
	public List<Map<String, Object>> getEmployeeGroupByArea(Integer groupArea) {
		String aCode = "substr(em.area,1,4 )";
		if(groupArea != null && groupArea.intValue() == 1){//按市级归类
			aCode = "substr(em.area,1,2)";
		}else if(groupArea == null || groupArea.intValue() == 2){//按市级归类
			aCode = "substr(em.area,1,4)";
		}else if(groupArea != null && groupArea.intValue() == 3){//按区级归类
			aCode = "substr(em.area,1,6)";
		}
		List<Map<String, Object>> returnData = new ArrayList<Map<String,Object>>();
		String sql = "SELECT "+aCode+", COUNT(em.id) as usercount FROM tb_employee AS em group by "+aCode+" order by usercount ASC";
		List<Object> ctli = entityManage.findBySql(sql.toString());
		Map<String, Integer> data = new HashMap<String, Integer>();
		for(int i= 0 ; i<ctli.size(); i++){
			Object[] ory = (Object[]) ctli.get(i);
			String areaName = "未知";
			Integer count = 0;
			if(ory[0] != null){//状态
				Integer area = 0;
				String code = ory[0].toString();
				if(groupArea != null && groupArea.intValue() == 1){//按省级归类
					area = Integer.parseInt(code)*10000;
					areaName= AreaHelp.getInstance().getProvince(area);
				}else if(groupArea == null || groupArea.intValue() == 2){//按市级归类
					area = Integer.parseInt(code)*100;
					areaName = AreaHelp.getInstance().getCity(area);
				}else if(groupArea != null && groupArea.intValue() == 3){//按区级归类
					area = Integer.parseInt(code);
					areaName = AreaHelp.getInstance().getAreaModel(code).getName();
				}
			}
			if(ory[1] != null){//人数
				count = Integer.parseInt(ory[1].toString());
			}
			if("未知".equals(areaName) && data.containsKey(areaName)){
				data.put(areaName, count+data.get(areaName));
			}else{
				data.put(areaName, count);
			}
			Map<String, Object> sub = new HashMap<String, Object>();
			sub.put("areaName", areaName);
			sub.put("count", data.get(areaName));
			returnData.add(sub);
		}
		return returnData;
	}
	
	@Override
	public Map<String, Object> getEmployeeGroupByAge() {
		Map<String, Object> data = new HashMap<String, Object>();
		String sql = "SELECT substr(em.idcard,7,4 ), COUNT(em.id) FROM tb_employee AS em group by substr(em.idcard,7,4 )";
		List<Object> ctli = entityManage.findBySql(sql.toString());
		for(int i= 0 ; i<ctli.size(); i++){
			Object[] ory = (Object[]) ctli.get(i);
			String key = "未知";
			int count = 0;
			if(ory[0] != null){//状态
				String year = ory[0].toString();
				key = getGroutAge(year);
			}
			if(ory[1] != null){//人数
				count = Integer.parseInt(ory[1].toString());
			}
			if(data.get(key) == null){
				data.put(key, count);
			}else{
				count += (int)data.get(key);
			}
			data.put(key, count);
		}
		return data;
	}
	
	
	@Override
	public Map<String, Object> getEmployeeGroupByWorkYear() {
		Map<String, Object> data = new HashMap<String, Object>();
		String sql = "SELECT em.workyears, COUNT(em.id) FROM tb_employee AS em group by em.workyears";
		List<Object> ctli = entityManage.findBySql(sql.toString());
		for(int i= 0 ; i<ctli.size(); i++){
			Object[] ory = (Object[]) ctli.get(i);
			String key = "未知";
			int count = 0;
			if(ory[0] != null){//状态
				int year = Integer.parseInt(ory[0].toString());
				key = getGroutWorkYear(year);
			}
			if(ory[1] != null){//人数
				count = Integer.parseInt(ory[1].toString());
			}
			if(data.get(key) == null){
				data.put(key, count);
			}else{
				count += (int)data.get(key);
			}
			data.put(key, count);
		}
		return data;
	}
	
	@Override
	public Map<String, Object> getEmployeeGroupBySex() {
		Map<String, Object> data = new HashMap<String, Object>();
		String sql = "SELECT em.sex, COUNT(em.id) FROM tb_employee AS em group by em.sex";
		List<Object> ctli = entityManage.findBySql(sql.toString());
		for(int i= 0 ; i<ctli.size(); i++){
			Object[] ory = (Object[]) ctli.get(i);
			String key = "";
			int count = 0;
			if(ory[0] != null){//状态
				int sex = Integer.parseInt(ory[0].toString());
				if(sex == 1){
					key = "男";
				}else if(sex == 2){
					key = "女";
				}else{
					key = "未知";
				}
			}else{
				key = "未知";
			}
			if(ory[1] != null){//人数
				count = Integer.parseInt(ory[1].toString());
			}
			if(data.get(key) == null){
				data.put(key, count);
			}else{
				count += (int)data.get(key);
			}
			data.put(key, count);
		}
		return data;
	}
	
	
	@Override
	public Map<String, Object> getEmployeeGroupByWorkState(String startTime, String endTime) {
		Map<String, Object> data = new HashMap<String, Object>();
		int count = 0;
		int workCount = 0;
		int noWorkCount = 0;
		Map<String, NullableType> fieldMappingType = new HashMap<String, NullableType>();
		fieldMappingType.put("usercount", Hibernate.INTEGER);
		
		
		//忙碌的
		StringBuffer sql = new StringBuffer("SELECT COUNT(e.id) AS usercount FROM tb_employee AS e");
		List<Object> ctli = entityManage.findBySql(sql.toString(),fieldMappingType);
		for(int i= 0 ; i<ctli.size(); i++){
			Integer ory = (Integer) ctli.get(i);
			if(ory != null){//总人数
				count = ory;
			}
		}
		
		//忙碌的
		sql = new StringBuffer("SELECT COUNT(DISTINCT(e.id)) AS usercount FROM tb_employee AS e, tb_em_task AS et WHERE e.userid=et.emid AND et.starttime<=NOW() AND et.endtime>=NOW() AND et.isstop IS NULL AND et.canceltime IS NULL");
		ctli = entityManage.findBySql(sql.toString(),fieldMappingType);
		for(int i= 0 ; i<ctli.size(); i++){
			Integer ory = (Integer) ctli.get(i);
			if(ory != null){//人数
				workCount = ory;
			}
		}
				
		data.put("workCount", workCount);//工作中
		data.put("noWorkCount", count - workCount);//空闲
		return data;
	}
	
	/**
	 * 1--2年、2--3年、3--5年、5--10年、10以上
		 * @description	
		 * @author  han1088
		 * @param year
		 * @return
	 */
	private String getGroutWorkYear(int year) {
		String data = "";
		try{
			if(year >= 0 && year < 1){
				data = "1年以下";
			}else if(year >= 1 && year < 2){
				data = "1--2年";
			}else if(year >= 2 && year < 3){
				data = "2--3年";
			}else if(year >= 3 && year < 5){
				data = "3--5年";
			}else if(year >= 5 && year <= 10){
				data = "5--10年";
			}else if(year > 10){
				data = "10年以上";
			}else{
				data = "未知";
			}
		}catch(Exception e){
			data = "未知";
		}
		return data;
	}

	/**
	 * 18--25、25--30、30--35、35--40、40--45、45--50、50以上
		 * @description	
		 * @author  han1088
		 * @param year
		 * @return
	 */
	private static String getGroutAge(String year){
		String data = "";
		try{
			int y = DateUtil.getYear(new Date());
			int age = y-Integer.parseInt(year);
			if(age >= 18 && age <= 25){
				data = "18--25";
			}else if(age > 25 && age <= 30){
				data = "25--30";
			}else if(age > 30 && age <= 35){
				data = "30--35";
			}else if(age > 35 && age <= 40){
				data = "35--40";
			}else if(age > 40 && age <= 45){
				data = "40--45";
			}else if(age > 45 && age <= 50){
				data = "45--50";
			}else if(age > 50){
				data = "50以上";
			}else{
				data = "未知";
			}
		}catch(Exception e){
			data = "未知";
		}
		return data;
	}

	@Override
	public Map<String, Integer> getAddEmployeeGroupByDate(String startTime,
			String endTime) {
		StringBuffer sql = new StringBuffer("SELECT substr(e.creattime,1,10 ), COUNT(e.id) FROM tb_employee AS e WHERE 1=1 ");
		if(StringUtils.isNotBlank(startTime)){
			sql.append(" AND e.creattime>='" + startTime + "'");
		}
		
		if(StringUtils.isNotBlank(endTime)){
			sql.append(" AND e.creattime<='" + endTime + " 23:59:59'");
		}
	
		sql.append("GROUP BY substr(e.creattime,1,10) ");
		
		List<Object> ctli = entityManage.findBySql(sql.toString());
		Map<String, Integer> data = new HashMap<String, Integer>();
		for(int i= 0 ; i<ctli.size(); i++){
			Object[] ory = (Object[]) ctli.get(i);
			String key = "";
			int count = 0;
			if(ory[0] != null){//状态
				key = ory[0].toString();//日期
			}
			if(ory[1] != null){//人数
				count = Integer.parseInt(ory[1].toString());
			}
			data.put(key, count);
		}
		return data;
	}
}
